MUSA 550 - Geospatial Data Science in Python

Final Project - Data Visualizations

Roujia Chen & Jiayi Zhao

Load packages and cleaned dataset

import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import geopandas as gpd
import altair as alt
import hvplot
import hvplot.pandas
import holoviews as hv
hv.extension("bokeh")
import warnings
warnings.filterwarnings('ignore')

from shapely import wkt

df = pd.read_csv('./df_final.csv')
# convert the column with strings (assuming the strings are in WKT or "Well Known Text" format) to actual geometries with:
# Ref: https://gis.stackexchange.com/questions/283677/geometry-data-in-geodataframe-will-not-plot/283678#283678
df['geometry'] = df['geometry'].apply(wkt.loads)
df = gpd.GeoDataFrame(df, crs='epsg:4326', geometry='geometry')
df.head()

	Country	Year	geometry	Continent	Stillbirth_Mor	Neonatal_Mor	Infant_Mor	Child_Mor	Water_Improved	Water_Unimproved	Water_Surface	Open_Defecation	Sanitation_Improved	Sanitation_Unimproved	Measles_First	DTP_Third	Hib_Third	Hep_B_Third	Polio_Third
0	Aruba	2010	POLYGON ((-69.99694 12.57758, -69.93639 12.531...	North America	NaN	NaN	NaN	NaN	97.305656	2.536543	0.157803	1.402614	97.672935	0.924450	NaN	NaN	NaN	NaN	NaN
1	Aruba	2011	POLYGON ((-69.99694 12.57758, -69.93639 12.531...	North America	NaN	NaN	NaN	NaN	97.587341	2.248239	0.164422	1.437889	97.608208	0.953903	NaN	NaN	NaN	NaN	NaN
2	Aruba	2012	POLYGON ((-69.99694 12.57758, -69.93639 12.531...	North America	NaN	NaN	NaN	NaN	97.869026	1.959936	0.171040	1.473163	97.543480	0.983356	NaN	NaN	NaN	NaN	NaN
3	Aruba	2013	POLYGON ((-69.99694 12.57758, -69.93639 12.531...	North America	NaN	NaN	NaN	NaN	97.869026	1.959936	0.171040	1.473163	97.543480	0.983356	NaN	NaN	NaN	NaN	NaN
4	Aruba	2014	POLYGON ((-69.99694 12.57758, -69.93639 12.531...	North America	NaN	NaN	NaN	NaN	97.869026	1.959936	0.171040	1.473163	97.543480	0.983356	NaN	NaN	NaN	NaN	NaN

Viz.01: Mortality rates

Year Trend for average mortality rates in all countries

mortality_per_year = df.groupby('Year', as_index=False)['Stillbirth_Mor', 'Neonatal_Mor', 
                                                        'Infant_Mor', 'Child_Mor'].mean()
chart_mor1 = mortality_per_year.hvplot.line(
    x='Year', 
    y=['Stillbirth_Mor', 'Neonatal_Mor', 'Infant_Mor', 'Child_Mor'],
    title='2010-2019 Average Mortality Rate across World (Deaths per 1000 new births)',
    ylabel = 'Deaths per 1000 new-births',
    xlim = [2010-0.5,2019+0.5]
)

scatter_plot = mortality_per_year.hvplot.scatter(
    x='Year',
    y=['Stillbirth_Mor', 'Neonatal_Mor', 'Infant_Mor', 'Child_Mor'])

chart_mor1 = chart_mor1*scatter_plot
chart_mor1

hvplot.save(chart_mor1, 'chart_mor1.html')

Averages Mortality Rates in different continents

# stacked bar chart
by_conti_mor = df.groupby("Continent")['Stillbirth_Mor','Neonatal_Mor','Infant_Mor','Child_Mor'].mean()
by_conti_mor = by_conti_mor.reset_index()
# convert wide format group-by result to be long format for plotting
by_conti_mor = by_conti_mor.melt('Continent', var_name='Mortality_rate', value_name='Proportion')
by_conti_mor['Proportion'] = pd.Series([round(val, 2) for val in by_conti_mor['Proportion']], index = by_conti_mor.index) # round two decimals
by_conti_mor.head(10)

	Continent	Mortality_rate	Proportion
0	Africa	Stillbirth_Mor	20.82
1	Asia	Stillbirth_Mor	10.51
2	Europe	Stillbirth_Mor	3.79
3	North America	Stillbirth_Mor	9.80
4	Oceania	Stillbirth_Mor	10.06
5	South America	Stillbirth_Mor	8.75
6	Africa	Neonatal_Mor	26.48
7	Asia	Neonatal_Mor	13.31
8	Europe	Neonatal_Mor	3.64
9	North America	Neonatal_Mor	11.01

# set colors
domain = ['Stillbirth_Mor','Neonatal_Mor','Infant_Mor','Child_Mor']
range_cl = ['#efeeb4','#dad873','#58b368','#309975']

# bar-chart
chart_mor2= alt.Chart(by_conti_mor).mark_bar().encode(
        y=alt.Y('Continent', axis=alt.Axis(title='Continent')),
        x=alt.X('Proportion:Q',axis=alt.Axis(title='Deaths per 1000 new-births')),
        color=alt.Color('Mortality_rate:O',legend=alt.Legend(title='Mortality_rate'),scale=alt.Scale(domain=domain, range=range_cl)),
        tooltip=[alt.Tooltip('Continent', title='Continent'), 
                 alt.Tooltip('Mortality_rate',title='Mortality_rate'),
                 alt.Tooltip('Proportion', title='Deaths per 1000 new-births')]
        ).properties(
        width = 600,
        height=300,
        title='10-Year Average Under-five Mortality Rate by Continent, 2010-2019 (Deaths per 1000 Living-Births)'
        ) 

chart_mor2

chart_mor2.save('chart_mor2.json')

Current geospatial distribution of child mortality by countries

# Extract centroid lon/lat for each country (for roughly locate each country)
df['centroid_x'] = df['geometry'].centroid.x
df['centroid_y'] = df['geometry'].centroid.y
df[['centroid_x','centroid_y','geometry']].head()

	centroid_x	centroid_y	geometry
0	-69.974201	12.516935	POLYGON ((-69.99694 12.57758, -69.93639 12.531...
1	-69.974201	12.516935	POLYGON ((-69.99694 12.57758, -69.93639 12.531...
2	-69.974201	12.516935	POLYGON ((-69.99694 12.57758, -69.93639 12.531...
3	-69.974201	12.516935	POLYGON ((-69.99694 12.57758, -69.93639 12.531...
4	-69.974201	12.516935	POLYGON ((-69.99694 12.57758, -69.93639 12.531...

by_contry_mor = df.groupby(["Country","Continent","Year","centroid_x","centroid_y"])['Child_Mor'].mean().reset_index()
by_contry_mor19 = by_contry_mor.loc[by_contry_mor['Year']==2019]
by_contry_mor19['pct_higher_2019mean']= 100*(by_contry_mor19['Child_Mor'] - by_contry_mor19['Child_Mor'].mean())/by_contry_mor19['Child_Mor'].mean()
by_contry_mor19

	Country	Continent	Year	centroid_x	centroid_y	Child_Mor	pct_higher_2019mean
9	Afghanistan	Asia	2019	66.008447	33.836267	14.427644	113.081260
19	Albania	Europe	2019	20.053819	41.142482	1.077409	-84.087797
29	Algeria	Africa	2019	2.653091	28.148890	3.368520	-50.250474
47	Andorra	Europe	2019	1.560756	42.541327	0.135706	-97.995763
57	Angola	Africa	2019	17.536985	-12.292316	25.801267	281.057802
...	...	...	...	...	...	...	...
2028	Venezuela (Bolivarian Republic of)	South America	2019	-66.168840	7.127732	3.226884	-52.342296
2038	Viet Nam	Asia	2019	106.300935	16.642183	4.116041	-39.210365
2064	Yemen	Asia	2019	47.590602	15.905421	15.461924	128.356491
2074	Zambia	Africa	2019	27.774212	-13.460110	20.100783	196.867600
2084	Zimbabwe	Africa	2019	29.851885	-19.002537	16.836098	148.651611

197 rows × 7 columns

chart_mor3 = alt.Chart(by_contry_mor19).mark_circle(size=60).encode(
    x =alt.X('centroid_x:Q',axis=alt.Axis(title='Longitude')),
    y = alt.Y('centroid_y:Q',axis=alt.Axis(title='Latitude')),
    color=alt.Color('Continent'),
    tooltip=['Country',alt.Tooltip('Child_Mor',title='1-4-yr-old mortality rate'),
             alt.Tooltip('pct_higher_2019mean', title='Percentage higher than 2019 average')],
    size=alt.Size('pct_higher_2019mean',scale=alt.Scale(range=[10, 1000]))
     ).properties(
        width = 600,
        height=400,
        title='1-4-Year-Old Child Mortality Rate (sized by percentage higher than world average)'
        ).interactive()

chart_mor3

chart_mor3.save('chart_mor3.json')

Viz.02: Vaccination

Year Trend for average vaccination rates in all countries

vac_per_year = df.groupby(['Year', 'Continent'])['Measles_First','DTP_Third','Hib_Third','Hep_B_Third','Polio_Third'].mean()
vac_per_year = vac_per_year.reset_index()

chart_vac1 = vac_per_year.hvplot.line(
    x='Year', 
    y=['Measles_First','DTP_Third','Hib_Third','Hep_B_Third','Polio_Third'],
    groupby = 'Continent',
    title='2010-2019 Average Percentage of Vaccinated Surviving Infants across World',
    ylabel = '% of vaccinated surviving infants',
    xlim = [2010-0.5,2019+0.5]
)

scatter_plot = vac_per_year.hvplot.scatter(
    x='Year',
    y=['Measles_First','DTP_Third','Hib_Third','Hep_B_Third','Polio_Third'],
    groupby='Continent')

chart_vac1 = chart_vac1*scatter_plot
chart_vac1

hvplot.save(chart_vac1, 'chart_vac1.html')

Third dose of DTP as the main indicator: Vaccination rate of DTP-3rd-dose across countries, 2019

by_contry_vac = df.groupby(["Country","Continent","Year","centroid_x","centroid_y"])['DTP_Third'].mean().reset_index()
by_contry_vac19 = by_contry_vac.loc[by_contry_vac['Year']==2019]
by_contry_vac19['pct_lower_2019mean']= (-100)*(by_contry_vac19['DTP_Third'] - by_contry_vac19['DTP_Third'].mean())/by_contry_vac19['DTP_Third'].mean()
by_contry_vac19

	Country	Continent	Year	centroid_x	centroid_y	DTP_Third	pct_lower_2019mean
9	Afghanistan	Asia	2019	66.008447	33.836267	66.0	25.369903
19	Albania	Europe	2019	20.053819	41.142482	99.0	-11.945146
29	Algeria	Africa	2019	2.653091	28.148890	91.0	-2.899074
47	Andorra	Europe	2019	1.560756	42.541327	99.0	-11.945146
57	Angola	Africa	2019	17.536985	-12.292316	57.0	35.546734
...	...	...	...	...	...	...	...
2028	Venezuela (Bolivarian Republic of)	South America	2019	-66.168840	7.127732	64.0	27.631421
2038	Viet Nam	Asia	2019	106.300935	16.642183	89.0	-0.637556
2064	Yemen	Asia	2019	47.590602	15.905421	73.0	17.454589
2074	Zambia	Africa	2019	27.774212	-13.460110	88.0	0.493203
2084	Zimbabwe	Africa	2019	29.851885	-19.002537	90.0	-1.768315

197 rows × 7 columns

chart_vac2 = alt.Chart(by_contry_vac19).mark_circle(size=60).encode(
    x =alt.X('centroid_x:Q',axis=alt.Axis(title='Longitude')),
    y = alt.Y('centroid_y:Q',axis=alt.Axis(title='Latitude')),
    color=alt.Color('Continent'),
    tooltip=['Country','DTP_Third',alt.Tooltip('pct_lower_2019mean', title='Percentage lower than 2019 average')],
    size=alt.Size('pct_lower_2019mean',scale=alt.Scale(range=[10, 1000]))
     ).properties(
        width = 600,
        height=400,
        title='Third-dose DTP Vaccination Rate of Countries (sized by percentage lower than world average)'
        ).interactive()

chart_vac2

chart_vac2.save('chart_vac2.json')

Viz03. WASH - Sanitation

• 'Open_Defecation', 'Sanitation_Improved', 'Sanitation_Unimproved',

Sanitation stacked bar chart by continent

by_conti_sani = df.groupby("Continent")['Open_Defecation','Sanitation_Improved','Sanitation_Unimproved'].mean()
by_conti_sani = by_conti_sani.reset_index()
# convert wide format group-by result to be long format for plotting
a = by_conti_sani.melt('Continent', var_name='Sanitation', value_name='Proportion')
a['Proportion'] = pd.Series([round(val, 2) for val in a['Proportion']], index = a.index) # round two decimals
a

	Continent	Sanitation	Proportion
0	Africa	Open_Defecation	21.76
1	Asia	Open_Defecation	6.77
2	Europe	Open_Defecation	0.03
3	North America	Open_Defecation	2.57
4	Oceania	Open_Defecation	6.63
5	South America	Open_Defecation	3.88
6	Africa	Sanitation_Improved	55.05
7	Asia	Sanitation_Improved	86.90
8	Europe	Sanitation_Improved	97.71
9	North America	Sanitation_Improved	92.84
10	Oceania	Sanitation_Improved	86.60
11	South America	Sanitation_Improved	91.70
12	Africa	Sanitation_Unimproved	23.19
13	Asia	Sanitation_Unimproved	6.39
14	Europe	Sanitation_Unimproved	2.25
15	North America	Sanitation_Unimproved	4.65
16	Oceania	Sanitation_Unimproved	7.37
17	South America	Sanitation_Unimproved	4.49

# set colors
domain = ['Sanitation_Unimproved','Sanitation_Improved','Open_Defecation']
range_cl = ['#5E2830','#DB4437','#D99456']

# bar-chart
chart_sani1 = alt.Chart(a).mark_bar().encode(
        y=alt.Y('Continent', axis=alt.Axis(title='Continent')),
        x=alt.X('Proportion:Q',axis=alt.Axis(title='Proportion of population'),scale=alt.Scale(domain=[0, 100])),
        color=alt.Color('Sanitation:O',legend=alt.Legend(title='Sanitation'),scale=alt.Scale(domain=domain, range=range_cl)),
        tooltip=[alt.Tooltip('Continent', title='Continent'), 
                 alt.Tooltip('Sanitation',title='Sanitation'),
                 alt.Tooltip('Proportion', title='Proportion of population (%)')],
        order = alt.Order('Proportion', sort='descending')
        ).properties(
        width = 600,
        height=300,
        title='10-Year Average Proportion of Population Living with Sanitation Facilities (by Continent, 2010-2019)'
        ) 

chart_sani1

chart_sani1.save('chart_sani1.json')

Time trend and total population by year/continent

# import population data downloaded from World Bank
pop = pd.read_csv('population.csv')

col = np.r_[0,54:64] #index to keep (country code & 2010-2019 population)
pop = pop.iloc[:,col]
# to long format
pop = pop.melt('Country Name', var_name='Year', value_name='population') # in the unit of 1
pop.Year = pop.Year.astype(int)
pop

	Country Name	Year	population
0	Aruba	2010	101669.0
1	Afghanistan	2010	29185507.0
2	Angola	2010	23356246.0
3	Albania	2010	2913021.0
4	Andorra	2010	84449.0
...	...	...	...
2635	Kosovo	2019	1794248.0
2636	Yemen, Rep.	2019	29161922.0
2637	South Africa	2019	58558270.0
2638	Zambia	2019	17861030.0
2639	Zimbabwe	2019	14645468.0

2640 rows × 3 columns

# join population with df
df_wpop = pd.merge(df, pop, left_on = ['Country','Year'],right_on=['Country Name','Year'],how='left')
df_wpop['pop_od_sani'] = df_wpop['Open_Defecation']/100* df_wpop['population']
print(df_wpop.shape)
print(df.shape)
df_wpop[['Country','Open_Defecation','pop_od_sani']].head()

(2085, 24)
(2085, 21)

	Country	Open_Defecation	pop_od_sani
0	Aruba	1.402614	1426.023628
1	Aruba	1.437889	1467.307699
2	Aruba	1.473163	1510.875973
3	Aruba	1.473163	1519.700219
4	Aruba	1.473163	1528.760172

# Merge two series into one: continental mean od rate and sum of od population
a = df_wpop.groupby(["Continent","Year"])['Open_Defecation'].mean()
b = df_wpop.groupby(["Continent","Year"])['pop_od_sani'].sum()/1000000 # in million
od_sani = pd.concat([a, b],axis=1).reset_index()
od_sani = od_sani.dropna()
od_sani.head()

	Continent	Year	Open_Defecation	pop_od_sani
0	Africa	2010	24.003604	204.459569
1	Africa	2011	23.353672	202.144778
2	Africa	2012	22.726499	197.474426
3	Africa	2013	22.106463	194.802891
4	Africa	2014	21.530404	192.238525

import seaborn as sns
palette = sns.color_palette('Paired', 7).as_hex()
sns.palplot(palette)

# Time trend & Sanitation facilities (open defecation)
# Setup a brush selection
brush = alt.selection_interval(encodings=['x'])

# The top time trend
trend = alt.Chart().mark_line(point={
      "filled": False,
      "fill": "white"
    }).encode(
    x=alt.X('Year:O', scale=alt.Scale(zero=False),axis=alt.Axis(title='Year')),
    y=alt.Y('Open_Defecation:Q', scale=alt.Scale(zero=False),axis=alt.Axis(title='Proportion of population (%)')),
    color=alt.condition(brush, 'Continent:N', alt.value('lightgray'),legend=alt.Legend(title="Continent")),
    tooltip=["Year", "Open_Defecation:Q"]
    ).properties(
    selection=brush,
    width=700,
    title='Proportion of and Total Population Practising Open Defecation (by Continent, 2010-2017)')

# define color domain and range
domain=df_wpop.Continent.unique().tolist()
range_color = palette
# the bottom bar plot
bars = alt.Chart().mark_bar().encode(
    x=alt.X('pop_od_sani:Q',axis=alt.Axis(title='Total Population (in million)')),
    y=alt.Y('Continent:N',axis=alt.Axis(title='Continent')),
    color=alt.Color('Continent:N',scale=alt.Scale(domain=domain, range=range_color)),
    ).transform_filter(
    brush.ref() # the filter transform uses the selection
                # to filter the input data to this chart
).properties(
width=700
)

# vertical stacking
chart_sani2 = alt.vconcat(trend, bars, data=od_sani) 
# change font
chart_sani2

chart_sani2.save('chart_sani2.json')

Choropleth: open-defecation by year

# Slice to contain 5-year
df_1015 = df.loc[(df['Year']<2016) & (df['Continent'].isin(["Africa"]))]

# Geospatial distribution
# sanitation type choropleth
choropleth_sani = df_1015.hvplot(c='Open_Defecation',
                        frame_width=500, 
                        frame_height=400,
                        groupby = 'Year',
                        dynamic = False,
                        geo=True,
                        title = '2010-2015 Proportion of Population Practising Open_Defecation',
                        cmap='reds',
                        hover_cols=['Country','Open_Defecation'])
choropleth_sani

hvplot.save(choropleth_sani, 'choropleth_sani5yr.html',resources="inline")

Viz04. WASH - Water

Drinking water stacked bar chart by continent

by_conti_wat = df.groupby("Continent")['Water_Improved','Water_Unimproved','Water_Surface'].mean()
by_conti_wat = by_conti_wat.reset_index()
# convert wide format group-by result to be long format for plotting
a = by_conti_wat.melt('Continent', var_name='Water', value_name='Proportion')
a['Proportion'] = pd.Series([round(val, 2) for val in a['Proportion']], index = a.index) # round two decimals
a

	Continent	Water	Proportion
0	Africa	Water_Improved	77.94
1	Asia	Water_Improved	92.79
2	Europe	Water_Improved	99.02
3	North America	Water_Improved	95.95
4	Oceania	Water_Improved	92.58
5	South America	Water_Improved	95.52
6	Africa	Water_Unimproved	15.00
7	Asia	Water_Unimproved	4.46
8	Europe	Water_Unimproved	0.90
9	North America	Water_Unimproved	3.40
10	Oceania	Water_Unimproved	4.15
11	South America	Water_Unimproved	2.44
12	Africa	Water_Surface	7.06
13	Asia	Water_Surface	2.89
14	Europe	Water_Surface	0.08
15	North America	Water_Surface	0.65
16	Oceania	Water_Surface	3.32
17	South America	Water_Surface	2.14

# set colors
domain = ['Water_Improved','Water_Unimproved','Water_Surface']
range_cl = ['#002db3','#809fff','#bfcfff']

# bar-chart
chart_wat1 = alt.Chart(a).mark_bar().encode(
        y=alt.Y('Continent', axis=alt.Axis(title='Continent')),
        x=alt.X('Proportion:Q',axis=alt.Axis(title='Proportion of population'),scale=alt.Scale(domain=[0, 100])),
        color=alt.Color('Water:O',legend=alt.Legend(title='Drinking Water Source'),scale=alt.Scale(domain=domain, range=range_cl)),
        tooltip=[alt.Tooltip('Continent', title='Continent'), 
                 alt.Tooltip('Water',title='Drinking Water Source'),
                 alt.Tooltip('Proportion', title='Proportion of population (%)')],
        order = alt.Order('sum(Proportion):Q', sort='descending')
        ).properties(
        width = 600,
        height=300,
        title='10-Year Average Proportion of Population Using Drinking Water Sources (by Continent, 2010-2019)'
        ) 

chart_wat1

chart_wat1.save('chart_wat1.json')

Time trend and total population by year/continent

# Merge two series into one: continental mean od rate and sum of od population
df_wpop['pop_wt_improved'] = df_wpop['Water_Improved']/100* df_wpop['population']
a = df_wpop.groupby(["Continent","Year"])['Water_Improved'].mean()
b = df_wpop.groupby(["Continent","Year"])['pop_wt_improved'].sum()/1000000 # in million
ipr_water = pd.concat([a, b],axis=1).reset_index()
ipr_water = ipr_water.dropna()
ipr_water.head()

	Continent	Year	Water_Improved	pop_wt_improved
0	Africa	2010	74.993761	565.750407
1	Africa	2011	75.857604	590.004042
2	Africa	2012	76.706807	612.867211
3	Africa	2013	77.542355	638.783630
4	Africa	2014	78.358924	665.540806

# Setup a brush selection
brush = alt.selection_interval(encodings=['x'])

# The top time trend
trend = alt.Chart().mark_line(point={
      "filled": False,
      "fill": "white"
    }).encode(
    x=alt.X('Year:O', scale=alt.Scale(zero=False),axis=alt.Axis(title='Year')),
    y=alt.Y('Water_Improved:Q', scale=alt.Scale(zero=False),axis=alt.Axis(title='Proportion of population (%)')),
    color=alt.condition(brush, 'Continent:N', alt.value('lightgray'),legend=alt.Legend(title="Continent")),
    tooltip=["Year", "Water_Improved:Q"]
    ).properties(
    selection=brush,
    width=700,
    title='Proportion of and Total Population Using Improved Drinking Water Sources (by Continent, 2010-2017)')

# define color domain and range
domain=df_wpop.Continent.unique().tolist()
range_color = palette
# the bottom bar plot
bars = alt.Chart().mark_bar().encode(
    x=alt.X('pop_wt_improved:Q',axis=alt.Axis(title='Total Population (in million)')),
    y=alt.Y('Continent:N',axis=alt.Axis(title='Continent')),
    color=alt.Color('Continent:N',scale=alt.Scale(domain=domain, range=range_color)),
    ).transform_filter(
    brush.ref() # the filter transform uses the selection
                # to filter the input data to this chart
).properties(
width=700
)

# vertical stacking
chart_wat2 = alt.vconcat(trend, bars, data=ipr_water) 
# change font
chart_wat2

chart_wat2.save('chart_wat2.json')

Choropleth: using improved drinking water by year

choropleth_wat = df_1015.hvplot(c='Water_Improved',
                        frame_width=500, 
                        frame_height=400,
                        groupby = 'Year',
                        dynamic = False,
                        geo=True,
                        title = '2010-2019 Proportion of Population Using Improved Drinking Water Sources',
                        cmap='blues',
                        hover_cols=['Country','Water_Improved'])
choropleth_wat

hvplot.save(choropleth_wat, 'choropleth_wat5yrAF.html', resources="inline")